# -*- coding: UTF-8 -*-
import pymysql
import xlwt
import smtplib
import sys
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
from pymysql.converters import conversions
from pymysql.constants import FIELD_TYPE
from datetime import datetime, timedelta

# 创建一个自定义转换器字典，移除所有日期时间相关的自动转换
custom_conversions = conversions.copy()
date_time_types = [
    FIELD_TYPE.DECIMAL,
    FIELD_TYPE.NEWDECIMAL,
    FIELD_TYPE.DATETIME,
    FIELD_TYPE.DATE,
    FIELD_TYPE.TIMESTAMP,
]
for dtype in date_time_types:
    custom_conversions.pop(dtype, None)

# 连接管理
db = pymysql.connect(
    host="rm-2ze6e2g537b2oig6448850.mariadb.rds.aliyuncs.com",
    user="sysadm",
    password="CMZKamqnG9Ziv2T9",
    charset="utf8",
    conv=custom_conversions,  # 使用自定义转换器
)

# 开标
cursor = db.cursor()


def connect_to_server():
    smtp_server = "smtp.mxhichina.com"
    port = 80
    sender_email = "noreply@pingcl.com"
    password = "p2q7hDokTA%BpmG"

    server = smtplib.SMTP(smtp_server, port)
    server.ehlo()
    server.starttls()
    server.ehlo()
    server.login(sender_email, password)

    return server


def Send_Mail(table_name, messages):
    server = connect_to_server()

    sender_email = "noreply@pingcl.com"

    receiver_email = [
        "bocheng.zhou@pingcl.com",
        "taoming.zhou@pingcl.com",
        "runfa.li@pingcl.com",
    ]

    #    receiver_email = [
    #        "runfa.li@pingcl.com",
    #    ]
    subject = f"数据查询-{table_name}"
    message = f"您好！请查收数据查询结果，{messages}"

    email = MIMEMultipart()
    email["From"] = sender_email
    email["To"] = ", ".join(receiver_email)
    email["Subject"] = subject

    email.attach(MIMEText(message, "plain"))

    attachment_filename = f"{table_name}.xls"
    attachment = open(attachment_filename, "rb")

    base = MIMEBase("application", "octet-stream")
    base.set_payload((attachment).read())
    encoders.encode_base64(base)
    base.add_header(
        "Content-Disposition", "attachment; filename= %s" % attachment_filename
    )

    email.attach(base)

    server.send_message(email)
    server.quit()


def Select_35_Data():
    # 获取当前日期和时间
    now = datetime.now()
    now_str = now.strftime("%Y-%m-%d")
    yesterday = now - timedelta(days=1)
    yesterday_str = yesterday.strftime("%Y-%m-%d")
    table_name = now.strftime("%Y%m%d_%H%M")
    yesterday_message_str = yesterday.strftime("%Y-%m-%d 16:00:00")
    now_message_str = now.strftime("%Y-%m-%d 16:00:00")
    messages = f"当前数据为 {yesterday_message_str} 至 {now_message_str}。"

    all_sql = [
        f"SELECT order_id AS '订单ID', order_amount AS '订单金额', discount_amount AS '优惠金额', CASE order_status WHEN 'WAIT_BUYER_PAY' THEN '待支付' WHEN 'WAIT_SERVICE' THEN '待服务' WHEN 'WAIT_CONFIRM' THEN '待确认' WHEN 'CONFIRMED' THEN '用户已确认' WHEN 'FINISHED' THEN '交易成功' WHEN 'COMPLAINING' THEN '售后中' WHEN 'REFUNDING' THEN '退款中' WHEN 'CLOSED' THEN '交易关闭' END AS '订单状态', create_time AS '创建时间', business_id AS '业务ID', buyer_id AS '社区ID' FROM order_domain.t_order o WHERE o.business_class = 'incentive' AND o.create_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}';",
        f"SELECT td.order_id AS '订单ID', td.target_id AS '医生ID', u.real_name AS '医生姓名', td.source_code AS '任务ID', td.total_amount AS '明细金额', td.create_time AS '创建时间', td.remark AS '备注' FROM order_domain.t_order_detail td INNER JOIN order_domain.t_order o ON td.order_id = o.order_id LEFT JOIN user_domain.t_auth_abd_user u ON td.target_id = u.base_id WHERE o.business_class = 'incentive' AND o.create_time BETWEEN '{yesterday_message_str}' and '{now_message_str}';",
        f"SELECT '科普活动'as '业务类型',r.activity_id, t.name, IFNULL(ur.title, other_ur.title) title, r.user_id, u.real_name, CASE r.task_type WHEN 'POPULAR_CONTRIBUTE' THEN '投稿任务' WHEN 'POPULAR_DISSEMINATE' THEN '传播任务' WHEN 'POPULAR_DISSEMINATE_READ_NUM' THEN '传播内容阅读数任务' WHEN 'POPULAR_DISSEMINATE_MEDIA_EXPOSURE' THEN '传播媒体曝光量' WHEN 'POPULAR_VOTE' THEN '投票任务' WHEN 'POPULAR_VOTE_NUMBER' THEN '投票票数任务' WHEN 'POPULAR_VOTE_RANK' THEN '投票排名任务' END AS '奖励类型', r.order_id, r.reward_amount, r.actual_reward_time FROM activity_center_domain.t_activity_task_reward_record r LEFT JOIN activity_center_domain.t_activity t ON r.activity_id = t.id LEFT JOIN activity_center_domain.t_activity_task_user_record ur ON r.source_id = ur.id LEFT JOIN activity_center_domain.t_activity_task_user_record other_ur ON r.task_type != 'POPULAR_CONTRIBUTE' AND ur.base_record_id = other_ur.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE reward_status = 'GRANT' AND r.actual_reward_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT '内容邀约' AS '业务类型',r.activity_id, t.name, ur.content_name, r.user_id, u.real_name, CASE r.reward_type WHEN 'UNIFORM_PRICE' THEN '稿费' WHEN 'TITLE_PRICE' THEN '稿费' WHEN 'LABEL_PRICE' THEN '稿费' WHEN 'DISSEMINATE_PRICE' THEN '传播奖励' WHEN 'OTHER_PRICE' THEN '其他奖励' WHEN 'HIGH_QUALITY' THEN '优质奖励' END AS '奖励类型', r.order_id, r.reward_amount, r.create_time FROM biz_activity_invitation_domain.t_activity_task_reward_record r LEFT JOIN biz_activity_invitation_domain.t_activity t ON r.activity_id = t.id LEFT JOIN biz_activity_invitation_domain.t_activity_task_user_record ur ON r.source_id = ur.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE r.reward_status = 'GRANT' AND r.actual_reward_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT CASE i.interact_type WHEN 2 THEN '问卷' WHEN 3 THEN '答题' END AS '业务类型', b.id, b.name, ar.title, r.user_id, u.real_name, '稿费', r.order_id, r.reward_amount, r.actual_reward_time FROM biz_activity_invitation_domain.activity_content_reward_record r LEFT JOIN biz_activity_invitation_domain.activity_invitation_base b ON r.activity_id = b.id LEFT JOIN biz_activity_invitation_domain.activity_interact i ON b.id = i.activity_id LEFT JOIN biz_activity_invitation_domain.academic_activity_record ar ON r.content_id = ar.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE r.reward_status = 1 AND i.interact_type IN(2,3) AND r.actual_reward_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT '学术活动投稿类' AS '业务类型' ,b.id, b.name, cr.relation_name, r.user_id, u.real_name, CASE r.reward_type WHEN 8 THEN '稿费' WHEN 9 THEN '传播奖励' END AS '奖励类型' , r.order_id, r.reward_amount, r.actual_reward_time FROM biz_activity_invitation_domain.activity_content_reward_record r LEFT JOIN biz_activity_invitation_domain.activity_invitation_base b ON r.activity_id = b.id LEFT JOIN biz_activity_invitation_domain.activity_contribute_record cr ON r.content_id = cr.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE r.reward_status = 1 AND b.type = 7 AND r.actual_reward_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT '病例征集' AS '业务类型', b.id,b.name, mr.medical_name, r.user_id, mr.doctor_name, CASE r.reward_type WHEN 0 THEN '稿费' WHEN 1 THEN '优质奖励' END AS '奖励类型' , r.order_id, r.reward_amount, r.actual_reward_time FROM biz_activity_invitation_domain.activity_content_reward_record r LEFT JOIN biz_activity_invitation_domain.activity_invitation_base b ON r.activity_id = b.id LEFT JOIN community_domain.medical_record mr ON r.content_id = mr.id WHERE b.type = 3 AND r.reward_status = 1 AND r.actual_reward_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
    ]

    print(all_sql)

    book = xlwt.Workbook()  # 在循环外部创建一个Excel工作簿

    # 为每个查询定义一个Sheet名字
    sheet_names = [
        "激励业务订单",
        "激励业务订单明细",
        "科普活动",
        "内容邀约活动",
        "学术活动_互动类_问卷_答题",
        "学术活动投稿类",
        "病例征集",
    ]

    for index, sql in enumerate(all_sql):
        cursor.execute(sql)
        fields = [field[0] for field in cursor.description]

        all_data = cursor.fetchall()

        # 使用预定义的Sheet名字
        sheet = book.add_sheet(sheet_names[index])

        # 写入字段名称
        for col, field in enumerate(fields):
            sheet.write(0, col, field)

        # 从第一行开始写入数据
        for row, data in enumerate(all_data, start=1):
            for col, value in enumerate(data):
                sheet.write(row, col, value)

    # 保存Excel文件
    book.save(f"{table_name}.xls")

    # 关闭数据库连接
    db.close()

    Send_Mail(table_name, messages)


def Select_2_Data():
    # 获取当前日期和时间
    now = datetime.now()
    now_str = now.strftime("%Y-%m-%d")
    yesterday = now - timedelta(days=1)
    yesterday_str = yesterday.strftime("%Y-%m-%d")
    table_name = now.strftime("%Y%m%d_%H%M")
    yesterday_message_str = yesterday.strftime("%Y-%m-%d 10:00:00")
    now_message_str = now.strftime("%Y-%m-%d 16:00:00")
    messages = f"当前数据为 {yesterday_message_str} 至 {now_message_str}。"

    all_sql = [
        f"SELECT order_id AS '订单ID', order_amount AS '订单金额', discount_amount AS '优惠金额', CASE order_status WHEN 'WAIT_BUYER_PAY' THEN '待支付' WHEN 'WAIT_SERVICE' THEN '待服务' WHEN 'WAIT_CONFIRM' THEN '待确认' WHEN 'CONFIRMED' THEN '用户已确认' WHEN 'FINISHED' THEN '交易成功' WHEN 'COMPLAINING' THEN '售后中' WHEN 'REFUNDING' THEN '退款中' WHEN 'CLOSED' THEN '交易关闭' END AS '订单状态', create_time AS '创建时间', business_id AS '业务ID', buyer_id AS '社区ID' FROM order_domain.t_order o WHERE o.business_class = 'incentive' AND o.create_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}';",
        f"SELECT td.order_id AS '订单ID', td.target_id AS '医生ID', u.real_name AS '医生姓名', td.source_code AS '任务ID', td.total_amount AS '明细金额', td.create_time AS '创建时间', td.remark AS '备注' FROM order_domain.t_order_detail td INNER JOIN order_domain.t_order o ON td.order_id = o.order_id LEFT JOIN user_domain.t_auth_abd_user u ON td.target_id = u.base_id WHERE o.business_class = 'incentive' AND o.create_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}';",
        f"SELECT '科普活动'as '业务类型',r.activity_id, t.name, IFNULL(ur.title, other_ur.title) title, r.user_id, u.real_name, CASE r.task_type WHEN 'POPULAR_CONTRIBUTE' THEN '投稿任务' WHEN 'POPULAR_DISSEMINATE' THEN '传播任务' WHEN 'POPULAR_DISSEMINATE_READ_NUM' THEN '传播内容阅读数任务' WHEN 'POPULAR_DISSEMINATE_MEDIA_EXPOSURE' THEN '传播媒体曝光量' WHEN 'POPULAR_VOTE' THEN '投票任务' WHEN 'POPULAR_VOTE_NUMBER' THEN '投票票数任务' WHEN 'POPULAR_VOTE_RANK' THEN '投票排名任务' END AS '奖励类型', r.order_id, r.reward_amount, r.actual_reward_time FROM activity_center_domain.t_activity_task_reward_record r LEFT JOIN activity_center_domain.t_activity t ON r.activity_id = t.id LEFT JOIN activity_center_domain.t_activity_task_user_record ur ON r.source_id = ur.id LEFT JOIN activity_center_domain.t_activity_task_user_record other_ur ON r.task_type != 'POPULAR_CONTRIBUTE' AND ur.base_record_id = other_ur.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE reward_status = 'GRANT' AND r.actual_reward_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT '内容邀约' AS '业务类型',r.activity_id, t.name, ur.content_name, r.user_id, u.real_name, CASE r.reward_type WHEN 'UNIFORM_PRICE' THEN '稿费' WHEN 'TITLE_PRICE' THEN '稿费' WHEN 'LABEL_PRICE' THEN '稿费' WHEN 'DISSEMINATE_PRICE' THEN '传播奖励' WHEN 'OTHER_PRICE' THEN '其他奖励' WHEN 'HIGH_QUALITY' THEN '优质奖励' END AS '奖励类型', r.order_id, r.reward_amount, r.create_time FROM biz_activity_invitation_domain.t_activity_task_reward_record r LEFT JOIN biz_activity_invitation_domain.t_activity t ON r.activity_id = t.id LEFT JOIN biz_activity_invitation_domain.t_activity_task_user_record ur ON r.source_id = ur.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE r.reward_status = 'GRANT' AND r.actual_reward_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT CASE i.interact_type WHEN 2 THEN '问卷' WHEN 3 THEN '答题' END AS '业务类型', b.id, b.name, ar.title, r.user_id, u.real_name, '稿费', r.order_id, r.reward_amount, r.actual_reward_time FROM biz_activity_invitation_domain.activity_content_reward_record r LEFT JOIN biz_activity_invitation_domain.activity_invitation_base b ON r.activity_id = b.id LEFT JOIN biz_activity_invitation_domain.activity_interact i ON b.id = i.activity_id LEFT JOIN biz_activity_invitation_domain.academic_activity_record ar ON r.content_id = ar.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE r.reward_status = 1 AND i.interact_type IN(2,3) AND r.actual_reward_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT '学术活动投稿类' AS '业务类型' ,b.id, b.name, cr.relation_name, r.user_id, u.real_name, CASE r.reward_type WHEN 8 THEN '稿费' WHEN 9 THEN '传播奖励' END AS '奖励类型' , r.order_id, r.reward_amount, r.actual_reward_time FROM biz_activity_invitation_domain.activity_content_reward_record r LEFT JOIN biz_activity_invitation_domain.activity_invitation_base b ON r.activity_id = b.id LEFT JOIN biz_activity_invitation_domain.activity_contribute_record cr ON r.content_id = cr.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE r.reward_status = 1 AND b.type = 7 AND r.actual_reward_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT '病例征集' AS '业务类型', b.id,b.name, mr.medical_name, r.user_id, mr.doctor_name, CASE r.reward_type WHEN 0 THEN '稿费' WHEN 1 THEN '优质奖励' END AS '奖励类型' , r.order_id, r.reward_amount, r.actual_reward_time FROM biz_activity_invitation_domain.activity_content_reward_record r LEFT JOIN biz_activity_invitation_domain.activity_invitation_base b ON r.activity_id = b.id LEFT JOIN community_domain.medical_record mr ON r.content_id = mr.id WHERE b.type = 3 AND r.reward_status = 1 AND r.actual_reward_time BETWEEN '{yesterday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
    ]

    print(all_sql)

    book = xlwt.Workbook()  # 在循环外部创建一个Excel工作簿

    # 为每个查询定义一个Sheet名字
    sheet_names = [
        "激励业务订单",
        "激励业务订单明细",
        "科普活动",
        "内容邀约活动",
        "学术活动_互动类_问卷_答题",
        "学术活动投稿类",
        "病例征集",
    ]

    for index, sql in enumerate(all_sql):
        cursor.execute(sql)
        fields = [field[0] for field in cursor.description]

        all_data = cursor.fetchall()

        # 使用预定义的Sheet名字
        sheet = book.add_sheet(sheet_names[index])

        # 写入字段名称
        for col, field in enumerate(fields):
            sheet.write(0, col, field)

        # 从第一行开始写入数据
        for row, data in enumerate(all_data, start=1):
            for col, value in enumerate(data):
                sheet.write(row, col, value)

    # 保存Excel文件
    book.save(f"{table_name}.xls")

    # 关闭数据库连接
    db.close()

    Send_Mail(table_name, messages)


def Select_1_Data():
    # 获取当前日期和时间
    now = datetime.now()
    now_str = now.strftime("%Y-%m-%d")
    monday = now - timedelta(days=3)
    monday_str = monday.strftime("%Y-%m-%d")
    table_name = now.strftime("%Y%m%d_%H%M")
    monday_message_str = monday.strftime("%Y-%m-%d 16:00:00")
    now_message_str = now.strftime("%Y-%m-%d 10:00:00")
    messages = f"当前数据为 {monday_message_str} 至 {now_message_str}。"

    all_sql = [
        f"SELECT order_id AS '订单ID', order_amount AS '订单金额', discount_amount AS '优惠金额', CASE order_status WHEN 'WAIT_BUYER_PAY' THEN '待支付' WHEN 'WAIT_SERVICE' THEN '待服务' WHEN 'WAIT_CONFIRM' THEN '待确认' WHEN 'CONFIRMED' THEN '用户已确认' WHEN 'FINISHED' THEN '交易成功' WHEN 'COMPLAINING' THEN '售后中' WHEN 'REFUNDING' THEN '退款中' WHEN 'CLOSED' THEN '交易关闭' END AS '订单状态', create_time AS '创建时间', business_id AS '业务ID', buyer_id AS '社区ID' FROM order_domain.t_order o WHERE o.business_class = 'incentive' AND o.create_time BETWEEN '{monday_message_str}' AND '{now_message_str}';",
        f"SELECT td.order_id AS '订单ID', td.target_id AS '医生ID', u.real_name AS '医生姓名', td.source_code AS '任务ID', td.total_amount AS '明细金额', td.create_time AS '创建时间', td.remark AS '备注' FROM order_domain.t_order_detail td INNER JOIN order_domain.t_order o ON td.order_id = o.order_id LEFT JOIN user_domain.t_auth_abd_user u ON td.target_id = u.base_id WHERE o.business_class = 'incentive' AND o.create_time BETWEEN '{monday_message_str}' AND '{now_message_str}';",
        f"SELECT '科普活动'as '业务类型',r.activity_id, t.name, IFNULL(ur.title, other_ur.title) title, r.user_id, u.real_name, CASE r.task_type WHEN 'POPULAR_CONTRIBUTE' THEN '投稿任务' WHEN 'POPULAR_DISSEMINATE' THEN '传播任务' WHEN 'POPULAR_DISSEMINATE_READ_NUM' THEN '传播内容阅读数任务' WHEN 'POPULAR_DISSEMINATE_MEDIA_EXPOSURE' THEN '传播媒体曝光量' WHEN 'POPULAR_VOTE' THEN '投票任务' WHEN 'POPULAR_VOTE_NUMBER' THEN '投票票数任务' WHEN 'POPULAR_VOTE_RANK' THEN '投票排名任务' END AS '奖励类型', r.order_id, r.reward_amount, r.actual_reward_time FROM activity_center_domain.t_activity_task_reward_record r LEFT JOIN activity_center_domain.t_activity t ON r.activity_id = t.id LEFT JOIN activity_center_domain.t_activity_task_user_record ur ON r.source_id = ur.id LEFT JOIN activity_center_domain.t_activity_task_user_record other_ur ON r.task_type != 'POPULAR_CONTRIBUTE' AND ur.base_record_id = other_ur.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE reward_status = 'GRANT' AND r.actual_reward_time BETWEEN '{monday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT '内容邀约' AS '业务类型',r.activity_id, t.name, ur.content_name, r.user_id, u.real_name, CASE r.reward_type WHEN 'UNIFORM_PRICE' THEN '稿费' WHEN 'TITLE_PRICE' THEN '稿费' WHEN 'LABEL_PRICE' THEN '稿费' WHEN 'DISSEMINATE_PRICE' THEN '传播奖励' WHEN 'OTHER_PRICE' THEN '其他奖励' WHEN 'HIGH_QUALITY' THEN '优质奖励' END AS '奖励类型', r.order_id, r.reward_amount, r.create_time FROM biz_activity_invitation_domain.t_activity_task_reward_record r LEFT JOIN biz_activity_invitation_domain.t_activity t ON r.activity_id = t.id LEFT JOIN biz_activity_invitation_domain.t_activity_task_user_record ur ON r.source_id = ur.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE r.reward_status = 'GRANT' AND r.actual_reward_time BETWEEN '{monday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT CASE i.interact_type WHEN 2 THEN '问卷' WHEN 3 THEN '答题' END AS '业务类型', b.id, b.name, ar.title, r.user_id, u.real_name, '稿费', r.order_id, r.reward_amount, r.actual_reward_time FROM biz_activity_invitation_domain.activity_content_reward_record r LEFT JOIN biz_activity_invitation_domain.activity_invitation_base b ON r.activity_id = b.id LEFT JOIN biz_activity_invitation_domain.activity_interact i ON b.id = i.activity_id LEFT JOIN biz_activity_invitation_domain.academic_activity_record ar ON r.content_id = ar.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE r.reward_status = 1 AND i.interact_type IN(2,3) AND r.actual_reward_time BETWEEN '{monday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT '学术活动投稿类' AS '业务类型' ,b.id, b.name, cr.relation_name, r.user_id, u.real_name, CASE r.reward_type WHEN 8 THEN '稿费' WHEN 9 THEN '传播奖励' END AS '奖励类型' , r.order_id, r.reward_amount, r.actual_reward_time FROM biz_activity_invitation_domain.activity_content_reward_record r LEFT JOIN biz_activity_invitation_domain.activity_invitation_base b ON r.activity_id = b.id LEFT JOIN biz_activity_invitation_domain.activity_contribute_record cr ON r.content_id = cr.id LEFT JOIN user_domain.t_auth_abd_user u ON r.user_id = u.base_id WHERE r.reward_status = 1 AND b.type = 7 AND r.actual_reward_time BETWEEN '{monday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
        f"SELECT '病例征集' AS '业务类型', b.id,b.name, mr.medical_name, r.user_id, mr.doctor_name, CASE r.reward_type WHEN 0 THEN '稿费' WHEN 1 THEN '优质奖励' END AS '奖励类型' , r.order_id, r.reward_amount, r.actual_reward_time FROM biz_activity_invitation_domain.activity_content_reward_record r LEFT JOIN biz_activity_invitation_domain.activity_invitation_base b ON r.activity_id = b.id LEFT JOIN community_domain.medical_record mr ON r.content_id = mr.id WHERE b.type = 3 AND r.reward_status = 1 AND r.actual_reward_time BETWEEN '{monday_message_str}' AND '{now_message_str}' ORDER BY r.actual_reward_time desc;",
    ]

    print(all_sql)

    book = xlwt.Workbook()  # 在循环外部创建一个Excel工作簿

    # 为每个查询定义一个Sheet名字
    sheet_names = [
        "激励业务订单",
        "激励业务订单明细",
        "科普活动",
        "内容邀约活动",
        "学术活动_互动类_问卷_答题",
        "学术活动投稿类",
        "病例征集",
    ]

    for index, sql in enumerate(all_sql):
        cursor.execute(sql)
        fields = [field[0] for field in cursor.description]

        all_data = cursor.fetchall()

        # 使用预定义的Sheet名字
        sheet = book.add_sheet(sheet_names[index])

        # 写入字段名称
        for col, field in enumerate(fields):
            sheet.write(0, col, field)

        # 从第一行开始写入数据
        for row, data in enumerate(all_data, start=1):
            for col, value in enumerate(data):
                sheet.write(row, col, value)

    # 保存Excel文件
    book.save(f"{table_name}.xls")

    # 关闭数据库连接
    db.close()

    Send_Mail(table_name, messages)


if __name__ == "__main__":
    if len(sys.argv) > 1:
        option = sys.argv[1]
        if option == "35":
            Select_35_Data()
        elif option == "2":
            Select_2_Data()
        elif option == "1":
            Select_1_Data()
    else:
        print("输入参数错误，请检查！")
